Creating Views Using CREATE VIEW in MySQL

Course: MySQL Training

Topic: Creating Views Using CREATE VIEW

Course Link: MySQL Training Course

Demo Link: Register for a Free Course Demo

1. Introduction to MySQL Views

A view in MySQL is a virtual table created from the result of a SQL query. It allows users to access selected columns and rows from one or more underlying tables without having to write the same complex query repeatedly.

Unlike a regular table, a standard view generally does not store its own copy of the query results. Instead, MySQL stores the view definition and retrieves data from the underlying tables when the view is queried.

Views are useful for simplifying complex SQL queries, improving data organization, restricting access to sensitive columns, and providing consistent data representations for reports and applications.

Example Scenario

Suppose a company maintains an employees table containing employee IDs, names, departments, salaries, email addresses, and phone numbers. The HR reporting team only needs employee names and departments.

Instead of repeatedly selecting those columns, you can create a view named employee_directory that exposes only the required information.

CREATE VIEW employee_directory AS
SELECT employee_id, employee_name, department
FROM employees;

Users can then query the view just like a table:

SELECT *
FROM employee_directory;

2. Learning Objectives

After completing this topic, you will be able to:

• Understand what a view is and how it works in MySQL.
• Identify the advantages and limitations of using views.
• Create views using the CREATE VIEW statement.
• Select specific columns and rows through a view.
• Create views using joins, aggregate functions, and calculated columns.
• Use WITH CHECK OPTION to control updates through certain views.
• Modify, replace, inspect, and delete view definitions.
• Understand view security, performance, and troubleshooting considerations.
• Apply views to practical business reporting scenarios.

3. What Is a View in MySQL?

A view is a named SQL query that can be referenced as a table in other SQL statements. The view definition describes how MySQL should retrieve the data from the underlying tables.

3.1 Main Characteristics of Views

• Virtual table: A regular view represents data retrieved from underlying tables.
• Stored definition: MySQL stores the query definition of the view.
• Reusable query: The same query logic can be reused in multiple reports and applications.
• Column selection: A view can expose only the columns required by its users.
• Row filtering: A view can restrict results using a WHERE clause.
• Query composition: Views can be used in other SQL queries and, where supported, other view definitions.
• Underlying data: Changes to the source tables can affect the data displayed by the view.

3.2 View vs. Table

4. Understanding the CREATE VIEW Statement

The CREATE VIEW statement defines a view by associating a name with a SELECT query.

4.1 Basic Syntax

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

4.2 Syntax Explanation

4.3 Important Naming Rules

• Choose a descriptive name, such as active_customers or monthly_sales_summary.
• Avoid naming a view the same as an existing table in the same schema.
• Use clear column aliases for calculated columns and expressions.
• Make sure the source tables and referenced columns exist.
• Use explicit column names instead of relying on SELECT * when a stable view structure is important.

5. Preparing Sample Data

Before creating views, create sample tables and insert example records. The following dataset will be used throughout these notes.

5.1 Create a Database

CREATE DATABASE business_db;
USE business_db;

If you already have a database for practice, you can select it instead of creating a new one.

5.2 Create the Employees Table

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10,2),
    city VARCHAR(50),
    employment_status VARCHAR(20)
);

5.3 Insert Sample Records

INSERT INTO employees
(employee_id, employee_name, department, salary, city, employment_status)
VALUES
(1, 'Aarav Sharma', 'IT', 65000, 'Mumbai', 'Active'),
(2, 'Priya Verma', 'HR', 48000, 'Pune', 'Active'),
(3, 'Rahul Mehta', 'Finance', 58000, 'Mumbai', 'Active'),
(4, 'Sneha Patil', 'IT', 72000, 'Navi Mumbai', 'Active'),
(5, 'Karan Singh', 'Marketing', 45000, 'Pune', 'Inactive'),
(6, 'Neha Joshi', 'Finance', 62000, 'Mumbai', 'Active'),
(7, 'Vikram Rao', 'HR', 51000, 'Thane', 'Inactive');

You can verify the inserted data using:

SELECT *
FROM employees;

6. Creating Your First View

Assume the company wants a directory containing employee IDs, names, and departments without displaying salaries or other unnecessary information.

Step 1: Create the View

CREATE VIEW employee_directory AS
SELECT employee_id, employee_name, department
FROM employees;

Step 2: Retrieve Data from the View

SELECT *
FROM employee_directory;

Step 3: Retrieve Specific Columns

SELECT employee_name, department
FROM employee_directory;

Step 4: Filter the View Results

SELECT *
FROM employee_directory
WHERE department = 'IT';

Explanation: The view provides a reusable employee directory. Users can apply additional filtering, sorting, and other supported query operations when selecting from it.

7. Creating Views with Selected Columns

Views can expose only the columns needed for a particular task. This improves clarity and can help limit the data visible to users who have permission to access the view but not the underlying table.

7.1 Create an Employee Contact View

Suppose a directory application needs employee IDs, names, and cities.

CREATE VIEW employee_contacts AS
SELECT employee_id, employee_name, city
FROM employees;

Query the view:

SELECT *
FROM employee_contacts;

7.2 Create a Department-Specific View

CREATE VIEW it_employees AS
SELECT employee_id, employee_name, city
FROM employees
WHERE department = 'IT';

Query the view:

SELECT *
FROM it_employees;

This view returns employees whose department is IT. The view definition provides the department filter, so users do not need to repeat it in every query.

7.3 Why Explicit Column Selection Matters

Using explicit columns makes the view easier to understand and reduces the chance of accidentally exposing newly added source-table columns. It also makes the view's intended output more predictable for applications and reports.

8. Creating Views with WHERE Conditions

The WHERE clause restricts which rows are returned by a view. It is useful for creating views for active customers, open orders, specific regions, or employees meeting a business rule.

8.1 View of Active Employees

CREATE VIEW active_employees AS
SELECT employee_id, employee_name, department, city
FROM employees
WHERE employment_status = 'Active';

Retrieve active employees:

SELECT *
FROM active_employees;

8.2 View of High-Salary Employees

CREATE VIEW high_salary_employees AS
SELECT employee_id, employee_name, department, salary
FROM employees
WHERE salary >= 60000;

Retrieve the results in descending salary order:

SELECT *
FROM high_salary_employees
ORDER BY salary DESC;

8.3 View of Employees in a Specific City

CREATE VIEW mumbai_employees AS
SELECT employee_id, employee_name, department
FROM employees
WHERE city = 'Mumbai';

Business use case: A manager can use a city-specific view for location-based reporting without repeatedly writing the same condition.

9. Creating Views with Column Aliases

Column aliases allow you to give meaningful names to output columns. They are especially helpful when a view contains calculations, aggregate values, or columns whose original names are unclear.

9.1 Using Aliases

CREATE VIEW employee_salary_report AS
SELECT
    employee_id AS id,
    employee_name AS full_name,
    department AS department_name,
    salary AS monthly_salary
FROM employees;

Query the view:

SELECT id, full_name, department_name, monthly_salary
FROM employee_salary_report;

9.2 Calculated Columns

Suppose the company wants to estimate an annual salary by multiplying the recorded salary by 12. This example assumes the stored salary represents a monthly amount.

CREATE VIEW annual_salary_report AS
SELECT
    employee_id,
    employee_name,
    department,
    salary AS monthly_salary,
    salary * 12 AS estimated_annual_salary
FROM employees;

Retrieve the calculated values:

SELECT *
FROM annual_salary_report;

Calculated columns are computed from the underlying data when the view is queried. They do not automatically become stored columns in the original table.

10. Creating Views with Aggregate Functions

Aggregate functions summarize multiple rows. Views can use functions such as COUNT(), SUM(), AVG(), MIN(), and MAX() to provide reusable reports.

10.1 Employee Count by Department

CREATE VIEW department_employee_count AS
SELECT
    department,
    COUNT(*) AS employee_count
FROM employees
GROUP BY department;

Query the view:

SELECT *
FROM department_employee_count
ORDER BY employee_count DESC;

10.2 Average Salary by Department

CREATE VIEW department_salary_summary AS
SELECT
    department,
    COUNT(*) AS employee_count,
    AVG(salary) AS average_salary,
    MIN(salary) AS minimum_salary,
    MAX(salary) AS maximum_salary
FROM employees
GROUP BY department;

Retrieve departments with an average salary greater than 55,000:

SELECT *
FROM department_salary_summary
WHERE average_salary > 55000;

Important: Because the view contains aggregated results, it is generally not directly updatable. Treat this kind of view as a reporting interface rather than a way to edit individual employee records.

11. Creating Views with JOIN Operations

A view can combine information from multiple tables using joins. This is useful when business information is distributed across related tables.

11.1 Create a Departments Table

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50) NOT NULL,
    manager_name VARCHAR(100)
);

11.2 Insert Department Records

INSERT INTO departments
(department_id, department_name, manager_name)
VALUES
(1, 'IT', 'Anil Kapoor'),
(2, 'HR', 'Meera Shah'),
(3, 'Finance', 'Rohan Desai'),
(4, 'Marketing', 'Kavita Rao');

11.3 Create a View with an INNER JOIN

CREATE VIEW employee_department_details AS
SELECT
    e.employee_id,
    e.employee_name,
    e.salary,
    e.city,
    d.manager_name
FROM employees AS e
INNER JOIN departments AS d
    ON e.department = d.department_name;

Query the view:

SELECT *
FROM employee_department_details;

The join matches employee department names with department names in the departments table. An INNER JOIN returns only rows with matching department records.

11.4 Create a View with a LEFT JOIN

CREATE VIEW all_employee_department_details AS
SELECT
    e.employee_id,
    e.employee_name,
    e.department,
    d.manager_name
FROM employees AS e
LEFT JOIN departments AS d
    ON e.department = d.department_name;

This view keeps all employee rows, including those without a matching department record. For unmatched rows, the department manager value is NULL.

12. Creating Views for Business Reports

Views can standardize common business calculations and provide consistent data for dashboards, reports, and applications.

12.1 Payroll Summary View

CREATE VIEW payroll_summary AS
SELECT
    department,
    COUNT(*) AS employee_count,
    SUM(salary) AS total_monthly_salary,
    AVG(salary) AS average_monthly_salary
FROM employees
WHERE employment_status = 'Active'
GROUP BY department;

Query the payroll summary:

SELECT *
FROM payroll_summary
ORDER BY total_monthly_salary DESC;

This view calculates payroll figures for active employees only. The figures depend on the meaning and quality of the salary data in the source table.

12.2 Employee Status Report

CREATE VIEW employee_status_report AS
SELECT
    employment_status,
    COUNT(*) AS total_employees
FROM employees
GROUP BY employment_status;

Use this view to compare active and inactive employee counts.

12.3 City-Wise Employee Report

CREATE VIEW city_employee_report AS
SELECT
    city,
    COUNT(*) AS employee_count
FROM employees
GROUP BY city;

These reporting views reduce duplicated SQL logic and help teams use consistent calculations across multiple reports.

13. Using CREATE OR REPLACE VIEW

If a view already exists, running CREATE VIEW again with the same name normally results in an error. MySQL supports CREATE OR REPLACE VIEW to create a view or replace its existing definition.

13.1 Syntax

CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE condition;

13.2 Example

Suppose the original employee directory exposes employee IDs, names, and departments. You now want to include city information.

CREATE OR REPLACE VIEW employee_directory AS
SELECT employee_id, employee_name, department, city
FROM employees;

Verify the updated view:

SELECT *
FROM employee_directory;

13.3 Important Considerations

• Review the new query before replacing an existing view.
• Check whether applications or reports depend on the view's current columns.
• Changing column names or output structure may break dependent queries.
• Confirm permissions and security expectations after changing the definition.
• Use version control or migration scripts to track important database changes.

14. Modifying a View with ALTER VIEW

MySQL also supports ALTER VIEW to change the definition of an existing view without dropping and recreating it as a separate operation.

14.1 Syntax

ALTER VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE condition;

14.2 Example

ALTER VIEW employee_directory AS
SELECT employee_id, employee_name, department, city
FROM employees
WHERE employment_status = 'Active';

The view now returns only active employees and includes their city.

Verify the result:

SELECT *
FROM employee_directory;

Note: Choose either ALTER VIEW or CREATE OR REPLACE VIEW based on your database change workflow. Test changes carefully if other queries depend on the view.

15. Dropping a View Using DROP VIEW

If a view is no longer required, you can remove its definition using DROP VIEW. Dropping a view does not normally delete the underlying tables or the data stored in those tables.

15.1 Syntax

DROP VIEW view_name;

15.2 Example

DROP VIEW employee_contacts;

15.3 Drop Only If the View Exists

DROP VIEW IF EXISTS employee_contacts;

The IF EXISTS clause prevents an error if the specified view does not exist.

Use caution when dropping views because reports, applications, or other views may depend on them.

16. Inspecting and Listing Views

MySQL provides commands for checking which views exist and inspecting their definitions.

16.1 Show Views in the Current Database

SHOW FULL TABLES
WHERE Table_type = 'VIEW';

16.2 Display a View Definition

SHOW CREATE VIEW employee_directory;

This command displays the stored SQL definition and relevant view options.

16.3 Query Information Schema

SELECT
    TABLE_SCHEMA,
    TABLE_NAME,
    VIEW_DEFINITION
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA = 'business_db';

The availability of the full view definition depends on permissions and server configuration. Metadata visibility can also vary according to the user's privileges.

17. View Security and Access Control

Views can help limit which columns or rows a user can access, but they must be configured with appropriate database privileges. Simply creating a view does not automatically secure the underlying data.

17.1 Example: Restrict Access to Salary Data

Suppose employees should be able to view names and departments but should not be given access to the salary column.

CREATE VIEW public_employee_directory AS
SELECT employee_id, employee_name, department
FROM employees;

A database administrator can grant a user access to the view:

GRANT SELECT
ON business_db.public_employee_directory
TO 'report_user'@'localhost';

The account name and host must match an actual MySQL account. The administrator should also review existing privileges on the underlying table and other database objects.

17.2 DEFINER and SQL SECURITY

MySQL views support security modes that determine whose privileges are used when the view is accessed.

• SQL SECURITY DEFINER: The view generally executes using the privileges of the account specified as its definer.
• SQL SECURITY INVOKER: The view executes using the privileges of the user invoking it.

The default security mode is generally DEFINER unless otherwise specified. Select the mode carefully and ensure the relevant accounts have the intended permissions.

17.3 Example of an Invoker-Security View

CREATE SQL SECURITY INVOKER VIEW department_directory AS
SELECT employee_id, employee_name, department
FROM employees;

With invoker security, the querying user's privileges are used when accessing the underlying objects. The user may therefore need appropriate permissions on those objects.

Security best practice: Use least-privilege access, avoid exposing sensitive columns unnecessarily, and test views using the actual accounts that will access them.

18. Updating Data Through Views

Some MySQL views are updatable, meaning that certain INSERT, UPDATE, or DELETE operations performed through the view can affect the underlying table. Other views are read-only for practical purposes because their definitions are too complex or aggregate data.

18.1 Example of an Updatable View

CREATE VIEW employee_basic_info AS
SELECT employee_id, employee_name, department, city
FROM employees;

For a simple single-table view such as this one, an update may be possible if the user's privileges and the view definition allow it.

UPDATE employee_basic_info
SET city = 'Thane'
WHERE employee_id = 1;

This changes the corresponding value in the underlying employees table when the operation is permitted.

18.2 Conditions That Can Prevent Updates

A view may not be updatable, or may have restrictions, if its definition includes features such as:

• Aggregate functions such as SUM() or AVG().
• GROUP BY or HAVING clauses.
• DISTINCT.
• Some joins or derived-table constructions.
• Set operations such as UNION.
• Calculated expressions that do not map directly to an underlying column.

These are common restrictions rather than an exhaustive list. Actual updatability depends on the view definition and MySQL rules.

19. Using WITH CHECK OPTION

The WITH CHECK OPTION clause can be added to a view definition to ensure that inserts or updates performed through an updatable view satisfy the view's filtering condition.

19.1 Example

CREATE OR REPLACE VIEW active_employee_directory AS
SELECT employee_id, employee_name, department, employment_status
FROM employees
WHERE employment_status = 'Active'
WITH CHECK OPTION;

This view is designed to show active employees. The check option prevents a change made through the view from producing a row that no longer satisfies its filtering condition.

19.2 Why It Is Useful

• Helps enforce the view's row-filtering condition for writes made through the view.
• Reduces the chance of accidentally changing a row so that it disappears from the view.
• Supports business rules in suitable updatable-view scenarios.

Important: WITH CHECK OPTION does not make a non-updatable view updatable, and it is not a substitute for database constraints or comprehensive security controls.

20. Nested Views and Reusable Query Logic

A view can sometimes be defined using another view. This is known as a nested view. It can help separate common filtering logic from reporting logic, but excessive nesting can make queries difficult to maintain and optimize.

20.1 Example: Create a Base View

CREATE OR REPLACE VIEW active_employee_base AS
SELECT employee_id, employee_name, department, salary
FROM employees
WHERE employment_status = 'Active';

20.2 Create a Reporting View from the Base View

CREATE OR REPLACE VIEW active_department_salary_summary AS
SELECT
    department,
    COUNT(*) AS employee_count,
    AVG(salary) AS average_salary
FROM active_employee_base
GROUP BY department;

Query the reporting view:

SELECT *
FROM active_department_salary_summary;

The base view defines the active-employee filter, while the reporting view calculates departmental summaries. This makes the logic easier to reuse.

21. Views and Performance Optimization

Views can simplify SQL and improve maintainability, but they do not automatically make queries faster. A regular view generally uses the underlying tables when queried, so performance depends on the query definition, data volume, indexes, and MySQL's execution plan.

21.1 Use Indexes on Underlying Tables

If the view frequently filters or joins on particular columns, appropriate indexes on the source tables may improve performance.

CREATE INDEX idx_employees_department
ON employees(department);

Before adding an index, consider existing indexes, write overhead, storage costs, and the queries that actually run.

21.2 Avoid Unnecessary Columns

Select only the columns needed for the task. Returning unnecessary columns increases data transfer and may make reports harder to maintain.

21.3 Examine the Query Plan

EXPLAIN
SELECT *
FROM active_employees
WHERE department = 'IT';

EXPLAIN helps inspect how MySQL plans to execute a query. Check the plan and measure performance on representative data rather than assuming a view is automatically optimized.

21.4 Be Careful with Nested Views

Several layers of views can make execution plans harder to understand. Keep view definitions focused, and test the final queries under realistic workloads.

21.5 Understand Regular Views vs. Materialized Results

A regular MySQL view is not a stored snapshot of its query results. If a report requires persisted precomputed results, a separate table populated and refreshed through an appropriate process may be more suitable. This introduces extra work to keep the stored results synchronized with the source data.

22. Common Errors and Troubleshooting

22.1 Verify the Source Query First

If creating a view fails, run its SELECT statement independently before using it in CREATE VIEW.

SELECT employee_id, employee_name, department
FROM employees
WHERE employment_status = 'Active';

If the query fails, correct its table names, column names, permissions, or syntax before creating the view.

22.2 Verify the Current Database

SELECT DATABASE();

Make sure the intended schema is selected and the source tables exist in the correct location.

23. Best Practices for Creating Views

• Use meaningful names: Prefer names such as active_employees and department_salary_summary.
• Select explicit columns: Avoid SELECT * in long-lived views when the output structure should remain stable.
• Keep definitions focused: Build each view around a clear business purpose.
• Use clear aliases: Give calculated and aggregated columns understandable names.
• Apply appropriate filters: Include business conditions only when they are part of the intended view definition.
• Review permissions: Grant only the access users require and verify the view's security mode.
• Document dependencies: Record which reports, applications, and other views rely on important views.
• Test changes: Verify output and dependent queries after modifying a view.
• Inspect performance: Use execution plans and realistic test data to identify bottlenecks.
• Avoid excessive nesting: Keep dependencies understandable and manageable.
• Use source control: Maintain scripts for creating and changing views in production environments.
• Be cautious when dropping views: Confirm that dependent applications and reports will not break.

24. Practical Business Scenario

Scenario: A retail company needs separate database views for its HR team, department managers, and payroll reporting team.

24.1 HR Employee Directory

CREATE OR REPLACE VIEW hr_employee_directory AS
SELECT employee_id, employee_name, department, city
FROM employees;

This view provides basic employee information for directory use.

24.2 Active Employee View

CREATE OR REPLACE VIEW hr_active_employees AS
SELECT employee_id, employee_name, department
FROM employees
WHERE employment_status = 'Active';

This view filters out inactive employees.

24.3 Department Payroll Summary

CREATE OR REPLACE VIEW active_department_payroll AS
SELECT
    department,
    COUNT(*) AS employee_count,
    SUM(salary) AS total_salary,
    AVG(salary) AS average_salary
FROM employees
WHERE employment_status = 'Active'
GROUP BY department;

This view calculates salary totals and averages for active employees by department.

24.4 Retrieve the Reports

SELECT *
FROM hr_employee_directory;

SELECT *
FROM hr_active_employees;

SELECT *
FROM active_department_payroll
ORDER BY total_salary DESC;

Business benefit: Each team receives a consistent, purpose-specific representation of the data. Appropriate database permissions should still be configured for each team's role.

25. Mini Project: Employee Reporting System Using Views

Project objective: Create a small employee reporting system using MySQL views.

Project Requirements

1. Create an employee table with employee ID, name, department, salary, city, and employment status.
2. Insert at least ten sample employee records.
3. Create a view that displays basic employee information.
4. Create a view that displays only active employees.
5. Create a view for employees whose salary meets a selected threshold.
6. Create a department-wise employee count view.
7. Create a department-wise salary summary view.
8. Create a view that joins employees with department manager information.
9. Inspect the view definitions using SHOW CREATE VIEW.
10. Replace one view with an updated definition and test the result.
11. Drop a practice view using DROP VIEW IF EXISTS.
12. Test access permissions using an appropriately configured database account.

Expected Learning Outcome

By completing the project, you will gain practical experience in defining, querying, modifying, securing, and maintaining views for common business reporting tasks.

26. Practice Exercises

Complete the following exercises to reinforce your understanding.

1. Create a view named employee_names that displays only employee IDs and names.
2. Create a view named pune_employees that displays employees working in Pune.
3. Create a view named active_it_employees that includes only active IT employees.
4. Create a view named salary_above_50000 that displays employees earning more than 50,000.
5. Create a view that calculates estimated annual salaries using monthly salary multiplied by 12.
6. Create a view that counts employees in each department.
7. Create a view that displays average salary by department.
8. Create a view that joins employees with department manager information.
9. Modify a view using ALTER VIEW to include another column.
10. Replace an existing view using CREATE OR REPLACE VIEW.
11. Inspect the definition of a view using SHOW CREATE VIEW.
12. Drop a practice view using DROP VIEW IF EXISTS.

27. Practice Exercise Solutions

Solution 1: Employee Names

CREATE OR REPLACE VIEW employee_names AS
SELECT employee_id, employee_name
FROM employees;

Solution 2: Pune Employees

CREATE OR REPLACE VIEW pune_employees AS
SELECT employee_id, employee_name, department, city
FROM employees
WHERE city = 'Pune';

Solution 3: Active IT Employees

CREATE OR REPLACE VIEW active_it_employees AS
SELECT employee_id, employee_name, department
FROM employees
WHERE department = 'IT'
  AND employment_status = 'Active';

Solution 4: Salary Above 50,000

CREATE OR REPLACE VIEW salary_above_50000 AS
SELECT employee_id, employee_name, department, salary
FROM employees
WHERE salary > 50000;

Solution 5: Estimated Annual Salary

CREATE OR REPLACE VIEW employee_annual_salary AS
SELECT
    employee_id,
    employee_name,
    salary AS monthly_salary,
    salary * 12 AS estimated_annual_salary
FROM employees;

Solution 6: Employee Count by Department

CREATE OR REPLACE VIEW employee_count_by_department AS
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;

Solution 7: Average Salary by Department

CREATE OR REPLACE VIEW average_salary_by_department AS
SELECT department, AVG(salary) AS average_salary
FROM employees
GROUP BY department;

Solution 8: Employee and Manager Details

CREATE OR REPLACE VIEW employee_manager_details AS
SELECT
    e.employee_id,
    e.employee_name,
    e.department,
    d.manager_name
FROM employees AS e
LEFT JOIN departments AS d
    ON e.department = d.department_name;

Solution 9: Inspect a View

SHOW CREATE VIEW employee_names;

Solution 10: Drop a Practice View

DROP VIEW IF EXISTS employee_names;

28. Interview Questions and Answers

Q1. What is a view in MySQL?

Answer: A view is a named SQL query that presents data from one or more underlying tables as a virtual table.

Q2. Which statement is used to create a view?

Answer: The CREATE VIEW statement is used to define a view.

Q3. Does a regular MySQL view store a separate copy of its query results?

Answer: No. A regular view stores its definition rather than a separate copy of the result set.

Q4. What is the basic syntax for creating a view?

Answer:

CREATE VIEW view_name AS
SELECT column1, column2
FROM table_name;

Q5. Can a view contain a WHERE clause?

Answer: Yes. A WHERE clause can filter the rows returned by the view.

Q6. Can a view use multiple tables?

Answer: Yes. A view can use joins to combine data from multiple tables.

Q7. What is the difference between a table and a view?

Answer: A table stores data, whereas a regular view provides a named representation of a query against underlying objects.

Q8. What does CREATE OR REPLACE VIEW do?

Answer: It creates a view if one does not exist or replaces the definition of an existing view.

Q9. How can you modify a view?

Answer: You can use ALTER VIEW or CREATE OR REPLACE VIEW to change its definition.

Q10. How do you delete a view?

Answer: Use DROP VIEW view_name; or DROP VIEW IF EXISTS view_name;.

Q11. Can you update data through a view?

Answer: Some views are updatable, but this depends on the view definition, MySQL rules, and the user's privileges.

Q12. Are views with aggregate functions generally updatable?

Answer: Views containing aggregate functions and grouping clauses are generally not directly updatable.

Q13. What is WITH CHECK OPTION?

Answer: It requires inserts and updates performed through a suitable updatable view to satisfy the view's filtering condition.

Q14. How can you inspect a view's definition?

Answer: Use SHOW CREATE VIEW view_name;.

Q15. Do views always improve query performance?

Answer: No. Views improve query reuse and readability, but execution performance depends on the underlying query, indexes, data, and execution plan.

Q16. How can views help with security?

Answer: A view can expose selected columns or rows, and access can be granted on the view. Correct privileges and security settings are still essential.

Q17. What is a nested view?

Answer: A nested view is a view whose definition references another view.

Q18. What happens if an underlying table is changed?

Answer: Changes to source tables can affect whether a view works and what it returns. Removing or renaming referenced columns may invalidate the view.

Q19. Can you create a view using GROUP BY?

Answer: Yes. A view can use GROUP BY to provide summaries such as employee counts or average salaries by department.

Q20. What is a good practice when creating views?

Answer: Use descriptive names, select explicit columns, document dependencies, review access privileges, and test query performance.

29. Quick Revision Checklist

• Understand the meaning of a MySQL view.
• Know the syntax of CREATE VIEW.
• Create views using selected columns and filtering conditions.
• Use column aliases and calculated columns.
• Create views with aggregate functions and joins.
• Understand CREATE OR REPLACE VIEW and ALTER VIEW.
• Inspect definitions with SHOW CREATE VIEW.
• Remove views safely using DROP VIEW IF EXISTS.
• Understand view updatability and WITH CHECK OPTION.
• Review DEFINER, INVOKER, and database permissions.
• Use EXPLAIN to investigate query execution plans.
• Test view changes against dependent reports and applications.

30. Conclusion

Creating views using CREATE VIEW is an important MySQL skill for building reusable queries, simplifying reporting, and presenting data in a structured way. Views can select specific columns, filter rows, join related tables, calculate summary values, and provide consistent interfaces for applications and business teams.

To use views effectively, understand their limitations, check whether they are updatable, configure appropriate privileges, and test their performance against the underlying tables. With well-designed views, complex database logic becomes easier to maintain, reuse, and manage.

Continue Learning: Explore more database concepts through the MySQL Training Course or register for a course demo.